2015-04-01 - 17986.100 - Spec - WMS300 report #CrystalReportDevelopment

SPECIFICATIONS

17986.100 - WMS 300 report

Requirement Summary

Need to develop regional versions for WMS300 report for APAC & EMEA.

Admin Info

Purpose
 Need to develop regional versions for WMS 300 report.
Requested By
 Will Cartwright
Spec Created By
 Dhananjay G
Spec Created Date
 04/01/2015
Spec QA by
 Uday Kumar P
Objects
 WMS 300 regional versions for APAC & EMEA.
Document Status
 Complete

Estimates

SNo
 Activity
 Estimation in Hours
1
 Research
 20
2
 Documentation
 08
3
 Development/ Config
 150
4
 Unit test in DEV
 08
5
 Unit test in QUA
 08
6
 Standard Checklist for all regions
 08

TOTAL
 202

References

Prior Tickets

None

Documents

None

Spec Changes

SNo
 Change Details
 Requested By
 Requested On
 Notes if any
1.
 EMEA report - Sales Organization 2000 with Plants 0310,0315,0320,0325, 8030 and 9030.

APAC report - Sales Organization 3000 with Plants 0510, 8050 and 9050.
Sales Organization 3100 with Plants 0585 and 9058.
Sales Organization 3200 with Plants 0610,0615,0620,0625,8016 and 9016.
Sales Organization 4000 with Plants 0510,0550 and 9055.
Sales Organization 4100 with Plants 9056.
 Babu Lankipalli
 04/16/2015
 None
2.
 Calendar (financial calendar closes on 4th Sunday and calendar is last day of the month) which is taken into consideration.
 Babu Lankipalli
 05/14/2015
 Report considers Calendar month and not fiscal month. Eg. 28th April 2015 is considered as April 2015 based on calendar month and NOT period 5,2015.
3.
 Is there any possibility after successful execution send data (output) in email automatically?
 Babu Lankipalli
 05/14/2015
 Yes there is a possibility to trigger the Emails with report output in PDF format.
This is managed by Infra team.
4.
 Some of issues clarification between both regions:
1. Report in Emerging markets folder is missing data for May and June and
2. Monthly qty discrepancies between each folder
 Babu
 06/28/2015
 None
5.
 Sales Orgs 2100 and 2200 are added to all EMEA plants (0310, 0315, 0320,0325, 9030)
 Babu
 07/09/2015
 None
6.
 Included the '0550' plant to the sales Org '4100' in data source reports for APAC region and drop ship plants data validation.
 Babu
 07/09/2015
 None

Functional Requirement

Purpose/WHY:

Need to develop regional versions for WMS300 report for APAC & EMEA regions.

In Scope:

Need to develop regional versions for WMS300 report for APAC & EMEA regions.

Out of Scope:

WMS300 regional versions for North America & Japan not required etc.

Solution Summary

1. Need to hard code the datasource reports as follows:
i. EMEA report - Sales Organization 2000 with Plants 0310,0315,0320,0325, 8030 and 9030.
ii. APAC report - Sales Organization 3000 with Plants 0510, 8050 and 9050.
Sales Organization 3100 with Plants 0585 and 9058.
Sales Organization 3200 with Plants 0610,0615,0620,0625,8016 and 9016.
Sales Organization 4000 with Plants 0510,0550 and 9055.
Sales Organization 4100 with Plants 9056.
2. Need to create batch programs to combine first three CSV files to generate 'comb_sap_mww_metrics_ APAC' and last three CSV files to generate 'comb_sap_mww_metrics_EMEA'.
3. Need to validate regional report outputs with Production version.
4.Need to change the PLANT formula in WMS300 regional versions:
For WMS300 - EMEA version:
if {INV030_del_metrics_csv.PLANT} = "0310" then
"NEW ERA CAP COMPANY LIMITED"
else if {INV030_del_metrics_csv.PLANT} = "0315" then
"NEW ERA GB WEB"
else if {INV030_del_metrics_csv.PLANT} = "0320" then
"EERSEL"
else if {INV030_del_metrics_csv.PLANT} = "0325" then
"EERSEL WEB"
else if {INV030_del_metrics_csv.PLANT} = "8030" then
"SUBCONTRACT PLANT EMEA"
else if {INV030_del_metrics_csv.PLANT} = "9030" then
"DROPSHIP PLANT EMEA"
else
"MAINTAIN PLANT"

For WMS300 - APAC version:
if {INV030_del_metrics_csv.PLANT} = "0510" then
"HONG KONG"
else if {INV030_del_metrics_csv.PLANT} = "0550" then
"AUSTRALIA"
else if {INV030_del_metrics_csv.PLANT} = "0585" then
"KOREA"
else if {INV030_del_metrics_csv.PLANT} = "0610" then
"JAPAN WAREHOUSE"
else if {INV030_del_metrics_csv.PLANT} = "0615" then
"JAPAN ECOMM"
else if {INV030_del_metrics_csv.PLANT} = "0620" then
"JAPAN RETAIL"
else if {INV030_del_metrics_csv.PLANT} = "0625" then
"JAPAN FABRIC WAREHOUSE"
else if {INV030_del_metrics_csv.PLANT} = "8016" then
"JAPAN SUBCONTRACT"
else if {INV030_del_metrics_csv.PLANT} = "8050" then
"SUBCONTRACT PLANT APAC"
else if {INV030_del_metrics_csv.PLANT} = "9016" then
"DROPSHIP PLANT JAPAN”
else if {INV030_del_metrics_csv.PLANT} = "9050" then
"DROPSHIP PLANT APAC"
else if {INV030_del_metrics_csv.PLANT} = "9055" then
"DROPSHIP PLANT AUSTRALIA"
else if {INV030_del_metrics_csv.PLANT} = "9056" then
"DROPSHIP PLANT NEW ZEALAND"
else if {INV030_del_metrics_csv.PLANT} = "9058" then
"DROPSHIP PLANT KOREA"
else
"MAINTAIN PLANT"

Test Plan

Test ID
 Test Scenario
 Expected Results
1
 Report Format.
 Should be as per NEC crystal standard checklist.
2
 Execute Crystal Report in Crystal Designer tool after changes.
 Report should execute successfully in Crystal Designer tool.
3
 Schedule the Report in NEC BI Launch Pad.
 Report should execute successfully in NEC BI Launch Pad.

Solution Details

Here is the complete design for regionalizing the WMS300 report:
1. Six new datasource reports for APAC and EMEA regions executed at 11AM ET and 6:30PM ET respectively scheduled once a week:
i. CSV001APAC datasource report on LIKP,LIPS,MARA and VBUK tables with Sales Org filter on APAC(3000,3100,3200,4000 & 4100)
ii. CSV002APAC datasource report on EKBE,EKET,EKPO and MARA tables with Company Code filter on APAC(0060,,0050,0055,0056,0058).
iii. CSV003APAC datasource report on EKBE,EKET,EKPO and MARA tables with Company Code filter on APAC(0060,,0050,0055,0056,0058).
iv. CSV001EMEA datasource report on LIKP,LIPS,MARA and VBUK tables with Sales Org filter on EMEA(2000).
v. CSV002EMEA datasource report on EKBE,EKET,EKPO and MARA tables with Company Code filter on EMEA(0030).
vi. CSV003EMEA datasource report based on EKBE,EKET,EKPO and MARA tables with Company Code filter on EMEA(0030).

2. Six new CSV files as output from the above datasource reports placed in Menlo folder:
i. CSV001_sap_del_APAC.csv
ii. CSV002_sap_posto_APAC.csv
iii.CSV003_sap_posto_raw_APAC.csv
iv. CSV001_sap_del_EMEA.csv
v. CSV002_sap_posto_EMEA.csv
vi. CSV003_sap_posto_raw_EMEA.csv

3. Two new batch programs to combine the following CSV files executed sometime after the CSVs are generated(batch scheduling not known:
a. Batch Program 1 – To combine CSV001_sap_del_APAC, CSV002_sap_posto_APAC and CSV003_sap_posto_raw_APAC to generate ‘comb_sap_mww_metrics_APAC’.
b. Batch Program 2 – To combine CSV001_sap_del_ EMEA, CSV002_sap_posto_ EMEA and CSV003_sap_posto_raw_ EMEA to generate ‘comb_sap_mww_metrics_EMEA’.

4. Four new WMS300 reports as follows scheduled daily:
i. WMS300 in Japan folder based on comb_sap_mww_metrics_APAC’.
ii. WMS300 in Emerging APAC folder based on comb_sap_mww_metrics_APAC’.
iii. WMS300 in Emerging Latin Americas folder based on comb_sap_mww_metrics_EMEA’.
iv. WMS300 in EMEA folder based on comb_sap_mww_metrics_EMEA’.

Issues

Batch Programs need to be run by NEC to combine the files.